﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Customer_GetList_AuditUser]
AS
Begin
	Create Table #tmpCustTable (CompanyID int, AuditUser varchar(30))
	Declare myCur CURSOR FOR Select id From Company Order By id
	
	Declare @nID int
	Set @nID = 0

	Open myCur
	FETCH NEXT FROM myCur INTO @nID
	While @@fetch_status = 0
	Begin
		;WITH List1 As(
		Select Top 50 AuditUser
		From Customer_Auditlog
		Where CompanyId = @nID And [Type]=1
		Order By id Desc
		)
		,
		List2 As(
			Select List1.* From List1,Employee Where List1.AuditUser=Employee.UserName And Employee.State In(3,4)
		)

		Insert Into #tmpCustTable Select @nID,AuditUser From List2 Where Left(AuditUser,2)='20' And Left(AuditUser,4)>'2003' Group By AuditUser

		FETCH NEXT FROM myCur INTO @nID
	End

	Close myCur
	DEALLOCATE myCur

	Select * From #tmpCustTable
	Drop Table #tmpCustTable
End
